using System;
using System.Data;
using System.Data.OleDb;
using PCSComUtils.DataAccess;

using PCSComUtils.PCSExc;
using PCSComUtils.Common;

namespace PCSComSale.Order.DS
{
	public class SO_CommitInventoryMasterDS
	{
		public SO_CommitInventoryMasterDS()
		{
		}

		private const string THIS = "PCSComSale.Order.DS.SO_CommitInventoryMasterDS";
		public void Add(object pobjObjectVO)
		{
			const string METHOD_NAME = THIS + ".Add()";

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				SO_CommitInventoryMasterVO objObject = (SO_CommitInventoryMasterVO) pobjObjectVO;
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand("", oconPCS);

				strSql = "INSERT INTO SO_CommitInventoryMaster("
					+ SO_CommitInventoryMasterTable.COMMITMENTNO_FLD + ","
					+ SO_CommitInventoryMasterTable.COMMITDATE_FLD + ","
					+ SO_CommitInventoryMasterTable.SALEORDERMASTERID_FLD + ","
					+ SO_CommitInventoryMasterTable.EMPLOYEEID_FLD + ")"
					+ "VALUES(?,?,?,?)";

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.COMMITMENTNO_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[SO_CommitInventoryMasterTable.COMMITMENTNO_FLD].Value = objObject.CommitmentNo;

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.COMMITDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[SO_CommitInventoryMasterTable.COMMITDATE_FLD].Value = objObject.CommitDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.SALEORDERMASTERID_FLD, OleDbType.Integer));
				if (objObject.SaleOrderMasterID > 0)
					ocmdPCS.Parameters[SO_CommitInventoryMasterTable.SALEORDERMASTERID_FLD].Value = objObject.SaleOrderMasterID;
				else
					ocmdPCS.Parameters[SO_CommitInventoryMasterTable.SALEORDERMASTERID_FLD].Value = DBNull.Value;

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.EMPLOYEEID_FLD, OleDbType.Integer));
				if (objObject.EmployeeID > 0)
					ocmdPCS.Parameters[SO_CommitInventoryMasterTable.EMPLOYEEID_FLD].Value = objObject.EmployeeID;
				else
					ocmdPCS.Parameters[SO_CommitInventoryMasterTable.EMPLOYEEID_FLD].Value = DBNull.Value;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();

			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public int AddAndReturnID(object pobjObjectVO)
		{
			const string METHOD_NAME = THIS + ".AddAndReturnID()";

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				SO_CommitInventoryMasterVO objObject = (SO_CommitInventoryMasterVO) pobjObjectVO;
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand("", oconPCS);

				strSql = "INSERT INTO SO_CommitInventoryMaster("
					+ SO_CommitInventoryMasterTable.COMMITMENTNO_FLD + ","
					+ SO_CommitInventoryMasterTable.COMMITDATE_FLD + ","
					+ SO_CommitInventoryMasterTable.SALEORDERMASTERID_FLD + ","
					+ SO_CommitInventoryMasterTable.USERNAME_FLD + ","
					+ SO_CommitInventoryMasterTable.CONTACT_FLD + ","
					+ SO_CommitInventoryMasterTable.DELIVERYDATE_FLD + ","
					+ SO_CommitInventoryMasterTable.COMMENT_FLD + ","
					+ SO_CommitInventoryMasterTable.TAXCODE_FLD + ","
					+ SO_CommitInventoryMasterTable.EMPLOYEEID_FLD + ")"
					+ "VALUES(?,?,?,?,?,?,?,?,?)";

				strSql += " ; Select @@IDENTITY as NEWID";

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.COMMITMENTNO_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[SO_CommitInventoryMasterTable.COMMITMENTNO_FLD].Value = objObject.CommitmentNo;

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.COMMITDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[SO_CommitInventoryMasterTable.COMMITDATE_FLD].Value = objObject.CommitDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.SALEORDERMASTERID_FLD, OleDbType.Integer));
				if (objObject.SaleOrderMasterID > 0)
					ocmdPCS.Parameters[SO_CommitInventoryMasterTable.SALEORDERMASTERID_FLD].Value = objObject.SaleOrderMasterID;
				else
					ocmdPCS.Parameters[SO_CommitInventoryMasterTable.SALEORDERMASTERID_FLD].Value = DBNull.Value;

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.USERNAME_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[SO_CommitInventoryMasterTable.USERNAME_FLD].Value = objObject.Username;

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.CONTACT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[SO_CommitInventoryMasterTable.CONTACT_FLD].Value = objObject.Contact;

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.DELIVERYDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[SO_CommitInventoryMasterTable.DELIVERYDATE_FLD].Value = objObject.DeliveryDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.COMMENT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[SO_CommitInventoryMasterTable.COMMENT_FLD].Value = objObject.Comment;

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.TAXCODE_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[SO_CommitInventoryMasterTable.TAXCODE_FLD].Value = objObject.TaxCode;

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.EMPLOYEEID_FLD, OleDbType.Integer));
				if (objObject.EmployeeID > 0)
					ocmdPCS.Parameters[SO_CommitInventoryMasterTable.EMPLOYEEID_FLD].Value = objObject.EmployeeID;
				else
					ocmdPCS.Parameters[SO_CommitInventoryMasterTable.EMPLOYEEID_FLD].Value = DBNull.Value;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				return int.Parse(ocmdPCS.ExecuteScalar().ToString());
				//ocmdPCS.ExecuteNonQuery();
			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public void Delete(int pintID)
		{
			const string METHOD_NAME = THIS + ".Delete()";
			string strSql = String.Empty;
			strSql = "DELETE " + SO_CommitInventoryMasterTable.TABLE_NAME + " WHERE  " + "CommitInventoryMasterID" + "=" + pintID.ToString();
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
				ocmdPCS = null;
			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}


			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public object GetObjectVO(int pintID)
		{
			const string METHOD_NAME = THIS + ".GetObjectVO()";
			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				strSql = "SELECT "
					+ SO_CommitInventoryMasterTable.COMMITINVENTORYMASTERID_FLD + ","
					+ SO_CommitInventoryMasterTable.COMMITMENTNO_FLD + ","
					+ SO_CommitInventoryMasterTable.COMMITDATE_FLD + ","
					+ SO_CommitInventoryMasterTable.SALEORDERMASTERID_FLD + ","
					+ SO_CommitInventoryMasterTable.CONTACT_FLD + ","
					+ SO_CommitInventoryMasterTable.DELIVERYDATE_FLD + ","
					+ SO_CommitInventoryMasterTable.EMPLOYEEID_FLD
					+ " FROM " + SO_CommitInventoryMasterTable.TABLE_NAME
					+ " WHERE " + SO_CommitInventoryMasterTable.COMMITINVENTORYMASTERID_FLD + "=" + pintID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();

				SO_CommitInventoryMasterVO objObject = new SO_CommitInventoryMasterVO();

				while (odrPCS.Read())
				{
					if (odrPCS[SO_CommitInventoryMasterTable.COMMITINVENTORYMASTERID_FLD] != DBNull.Value)
						objObject.CommitInventoryMasterID = int.Parse(odrPCS[SO_CommitInventoryMasterTable.COMMITINVENTORYMASTERID_FLD].ToString().Trim());
					objObject.CommitmentNo = odrPCS[SO_CommitInventoryMasterTable.COMMITMENTNO_FLD].ToString().Trim();
					if (odrPCS[SO_CommitInventoryMasterTable.COMMITDATE_FLD] != DBNull.Value)
						objObject.CommitDate = DateTime.Parse(odrPCS[SO_CommitInventoryMasterTable.COMMITDATE_FLD].ToString().Trim());
					if (odrPCS[SO_CommitInventoryMasterTable.SALEORDERMASTERID_FLD] != DBNull.Value)
						objObject.SaleOrderMasterID = int.Parse(odrPCS[SO_CommitInventoryMasterTable.SALEORDERMASTERID_FLD].ToString().Trim());
					if (odrPCS[SO_CommitInventoryMasterTable.EMPLOYEEID_FLD] != DBNull.Value)
						objObject.EmployeeID = int.Parse(odrPCS[SO_CommitInventoryMasterTable.EMPLOYEEID_FLD].ToString().Trim());
					if (odrPCS[SO_CommitInventoryMasterTable.CONTACT_FLD] != DBNull.Value)
						objObject.Contact = odrPCS[SO_CommitInventoryMasterTable.CONTACT_FLD].ToString().Trim();
					if (odrPCS[SO_CommitInventoryMasterTable.DELIVERYDATE_FLD] != DBNull.Value)
						objObject.DeliveryDate = DateTime.Parse(odrPCS[SO_CommitInventoryMasterTable.DELIVERYDATE_FLD].ToString().Trim());
				}
				return objObject;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public DataRow GetMasterObject(int pintID)
		{
			const string METHOD_NAME = THIS + ".GetMasterObject()";
			
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT DISTINCT CommitDate, CommitmentNo, SM.Code, SM.TransDate, M.Comment,"
					+ " P.Code PARTY_CODE, P.Name PARTY_NAME, P.Address PARTY_ADD, ST.Code SALETYPE_CODE, PAY.Code PAYMENTTERM_CODE,"
					+ " D.MasterLocationID, D.LocationID, D.BinID, L.Code Location, B.Code Bin, Contact, M.TaxCode,"
                    + " Bank.BankAccount, Bank.BankName, Bank.BankID,"
					+ " M.DeliveryDate, M.CommitInventoryMasterID, M.SaleOrderMasterID"
					+ " FROM SO_CommitInventoryMaster M JOIN SO_SaleOrderMaster SM"
					+ " ON M.SaleOrderMasterID = SM.SaleOrderMasterID"
					+ " JOIN SO_CommitInventoryDetail D ON M.CommitInventoryMasterID = D.CommitInventoryMasterID"
					+ " JOIN MST_Location L ON D.LocationID = L.LocationID"
					+ " JOIN MST_Bin B ON D.BinID = B.BinID"
					+ " LEFT JOIN MST_Party P ON SM.PartyID = P.PartyID"
					+ " LEFT JOIN SO_SaleType ST ON SM.SaleTypeID = ST.SaleTypeID"
					+ " LEFT JOIN MST_PaymentTerm PAY ON SM.PaymentTermsID = PAY.PaymentTermID"
                    + " LEFT JOIN MST_Bank Bank ON M.BankID = Bank.BankID"
					+ " WHERE M.CommitInventoryMasterID =" + pintID;
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				DataTable dtbData = new DataTable(SO_CommitInventoryMasterTable.TABLE_NAME);
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dtbData);
				return dtbData.Rows[0];
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public void Update(object pobjObjecVO)
		{
			const string METHOD_NAME = THIS + ".Update()";

			SO_CommitInventoryMasterVO objObject = (SO_CommitInventoryMasterVO) pobjObjecVO;


			//prepare value for parameters
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				strSql = "UPDATE SO_CommitInventoryMaster SET "
					+ SO_CommitInventoryMasterTable.COMMITMENTNO_FLD + "=   ?" + ","
					+ SO_CommitInventoryMasterTable.COMMITDATE_FLD + "=   ?" + ","
					+ SO_CommitInventoryMasterTable.CONTACT_FLD + "=   ?" + ","
					+ SO_CommitInventoryMasterTable.DELIVERYDATE_FLD + "=   ?" + ","
					+ SO_CommitInventoryMasterTable.EMPLOYEEID_FLD + "=   ?" + ","
					+ SO_CommitInventoryMasterTable.USERNAME_FLD + "=   ?" + ","
					+ SO_CommitInventoryMasterTable.COMMENT_FLD + "=   ?" + ","
					+ SO_CommitInventoryMasterTable.TAXCODE_FLD + "=   ?" + ","
					+ SO_CommitInventoryMasterTable.SALEORDERMASTERID_FLD + "=   ?"
					+ " WHERE " + SO_CommitInventoryMasterTable.COMMITINVENTORYMASTERID_FLD + "= ?";

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.COMMITMENTNO_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[SO_CommitInventoryMasterTable.COMMITMENTNO_FLD].Value = objObject.CommitmentNo;

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.COMMITDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[SO_CommitInventoryMasterTable.COMMITDATE_FLD].Value = objObject.CommitDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.CONTACT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[SO_CommitInventoryMasterTable.CONTACT_FLD].Value = objObject.Contact;

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.DELIVERYDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[SO_CommitInventoryMasterTable.DELIVERYDATE_FLD].Value = objObject.DeliveryDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.EMPLOYEEID_FLD, OleDbType.Integer));
				if (objObject.EmployeeID > 0)
					ocmdPCS.Parameters[SO_CommitInventoryMasterTable.EMPLOYEEID_FLD].Value = objObject.EmployeeID;
				else
					ocmdPCS.Parameters[SO_CommitInventoryMasterTable.EMPLOYEEID_FLD].Value = DBNull.Value;

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.USERNAME_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[SO_CommitInventoryMasterTable.USERNAME_FLD].Value = objObject.Username;

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.COMMENT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[SO_CommitInventoryMasterTable.COMMENT_FLD].Value = objObject.Comment;

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.TAXCODE_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[SO_CommitInventoryMasterTable.TAXCODE_FLD].Value = objObject.TaxCode;

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.SALEORDERMASTERID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[SO_CommitInventoryMasterTable.SALEORDERMASTERID_FLD].Value = objObject.SaleOrderMasterID;

				ocmdPCS.Parameters.Add(new OleDbParameter(SO_CommitInventoryMasterTable.COMMITINVENTORYMASTERID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[SO_CommitInventoryMasterTable.COMMITINVENTORYMASTERID_FLD].Value = objObject.CommitInventoryMasterID;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
			}
			catch (OleDbException ex)
			{
				if (ex.Errors.Count > 1)
				{
					if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
						throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
					else
						throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
				else
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}

		}

		public DataSet List()
		{
			const string METHOD_NAME = THIS + ".List()";
			DataSet dstPCS = new DataSet();


			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;

				strSql = "SELECT "
					+ SO_CommitInventoryMasterTable.COMMITINVENTORYMASTERID_FLD + ","
					+ SO_CommitInventoryMasterTable.COMMITMENTNO_FLD + ","
					+ SO_CommitInventoryMasterTable.COMMITDATE_FLD + ","
					+ SO_CommitInventoryMasterTable.SALEORDERMASTERID_FLD + ","
					+ SO_CommitInventoryMasterTable.EMPLOYEEID_FLD
					+ " FROM " + SO_CommitInventoryMasterTable.TABLE_NAME;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS, SO_CommitInventoryMasterTable.TABLE_NAME);

				return dstPCS;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}


		}
		public DataSet ListCommittedInventoryMasterBySaleOrderMasterID(int pintSaleOrderMasterID)
		{
			const string METHOD_NAME = THIS + ".List()";
			DataSet dstPCS = new DataSet();


			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT "
					+ SO_CommitInventoryMasterTable.COMMITINVENTORYMASTERID_FLD + ","
					+ SO_CommitInventoryMasterTable.COMMITMENTNO_FLD + ","
					+ SO_CommitInventoryMasterTable.COMMITDATE_FLD + ","
					+ SO_CommitInventoryMasterTable.SALEORDERMASTERID_FLD + ","
					+ SO_CommitInventoryMasterTable.EMPLOYEEID_FLD
					+ " FROM " + SO_CommitInventoryMasterTable.TABLE_NAME
					+ " WHERE " + SO_CommitInventoryMasterTable.SALEORDERMASTERID_FLD + " = " + pintSaleOrderMasterID;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS, SO_CommitInventoryMasterTable.TABLE_NAME);

				return dstPCS;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}


		}

		public void UpdateDataSet(DataSet pData)
		{
			const string METHOD_NAME = THIS + ".UpdateDataSet()";
			string strSql;
			OleDbConnection oconPCS = null;
			OleDbCommandBuilder odcbPCS;
			OleDbDataAdapter odadPCS = new OleDbDataAdapter();

			try
			{
				strSql = "SELECT "
					+ SO_CommitInventoryMasterTable.COMMITINVENTORYMASTERID_FLD + ","
					+ SO_CommitInventoryMasterTable.COMMITMENTNO_FLD + ","
					+ SO_CommitInventoryMasterTable.COMMITDATE_FLD + ","
					+ SO_CommitInventoryMasterTable.SALEORDERMASTERID_FLD + ","
					+ SO_CommitInventoryMasterTable.EMPLOYEEID_FLD
					+ "  FROM " + SO_CommitInventoryMasterTable.TABLE_NAME;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				odadPCS.SelectCommand = new OleDbCommand(strSql, oconPCS);
				odcbPCS = new OleDbCommandBuilder(odadPCS);
				pData.EnforceConstraints = false;
				odadPCS.Update(pData, SO_CommitInventoryMasterTable.TABLE_NAME);

			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
				}

				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public void AutoDeleteCommitMaster()
		{
			const string METHOD_NAME = THIS + ".UpdateDataSet()";
			string strSql;
			OleDbConnection oconPCS = null;
			try
			{
				strSql =	" Delete " +
							" From " + SO_CommitInventoryMasterTable.TABLE_NAME +
							" Where (Select count (*)" +
							" From " + SO_CommitInventoryDetailTable.TABLE_NAME +
							" Where "+ SO_CommitInventoryDetailTable.TABLE_NAME + "." + SO_CommitInventoryDetailTable.COMMITINVENTORYMASTERID_FLD + " = " + SO_CommitInventoryMasterTable.TABLE_NAME + "." + SO_CommitInventoryMasterTable.COMMITINVENTORYMASTERID_FLD + ") = 0";

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				OleDbCommand ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
				ocmdPCS = null;
			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
				}

				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}
		public DataTable GetReportData(int pintMasterID)
		{
			const string METHOD_NAME = THIS + ".GetReportData()";
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT CommitDate, M.DeliveryDate, V.Name, M.Contact, M.TaxCode, Address, SM.Code SONo, T.Code PaymentTerm, SM.TransDate,"
					+ " P.Description, U.Code UM, P.Code, CommitQuantity, Price, Price * CommitQuantity Amount,"
                    + " (ISNULL(VATPercent,0) * Price * CommitQuantity)/100 VATAmount, Bank.BankAccount, Bank.BankName,"
					+ " (SELECT Name FROM MST_Employee WHERE EmployeeID = " + SystemProperty.EmployeeID + ") Fullname, M.Comment"
					+ " FROM SO_CommitInventoryMaster M JOIN SO_CommitInventoryDetail D"
					+ " ON M.CommitInventoryMasterID = D.CommitInventoryMasterID"
					+ " JOIN ITM_Product P ON D.ProductID = P.ProductID"
					+ " JOIN SO_DeliverySchedule S ON D.DeliveryScheduleID = S.DeliveryScheduleID"
					+ " JOIN SO_SaleOrderMaster SM ON M.SaleOrderMasterID = SM.SaleOrderMasterID"
					+ " JOIN SO_SaleOrderDetail SD ON SM.SaleOrderMasterID = SD.SaleOrderMasterID"
					+ " AND SD.SaleOrderDetailID = S.SaleOrderDetailID"
					+ " LEFT JOIN MST_Party V ON SM.PartyID = V.PartyID"
					+ " LEFT JOIN MST_UnitOfMeasure U ON D.SellingUMID = U.UnitOfMeasureID"
					+ " LEFT JOIN MST_PaymentTerm T ON SM.PaymentTermsID = T.PaymentTermID"
					+ " LEFT JOIN MST_Bank Bank ON M.BankID = Bank.BankID"
					+ " WHERE M.CommitInventoryMasterID = " + pintMasterID;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				DataTable dtbData = new DataTable(SO_CommitInventoryMasterTable.TABLE_NAME);
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dtbData);

				return dtbData;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}
	}
}
